/*
Copyright 2024 MySQL Parser Project

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/

package main

import (
	"fmt"
	"log"

	"mysql-parser/sqlparser"
)

func main() {
	fmt.Println("=== JOIN查询示例 ===")
	fmt.Println()

	// INNER JOIN示例
	fmt.Println("=== INNER JOIN示例 ===")
	innerJoinQueries := []string{
		"SELECT u.name, p.title FROM users u INNER JOIN posts p ON u.id = p.user_id",
		"SELECT o.id, u.name, p.name FROM orders o INNER JOIN users u ON o.user_id = u.id INNER JOIN products p ON o.product_id = p.id",
		"SELECT u.name, COUNT(p.id) FROM users u INNER JOIN posts p ON u.id = p.user_id GROUP BY u.id",
		"SELECT c.name, p.title FROM categories c INNER JOIN products p ON c.id = p.category_id WHERE p.price > 100",
	}

	for i, sql := range innerJoinQueries {
		fmt.Printf("INNER JOIN示例 %d: %s\n", i+1, sql)
		
		// 解析SQL
		stmt, err := sqlparser.Parse(sql)
		if err != nil {
			log.Printf("解析错误: %v", err)
			continue
		}

		// 提取表名
		tables := sqlparser.ExtractAllTables(stmt)
		fmt.Printf("  涉及的表: %v\n", tables)

		// 表分析
		tableAnalysis := sqlparser.AnalyzeTables(stmt)
		fmt.Printf("  表分析: 表数量=%d, 有连接=%v, 有子查询=%v\n",
			tableAnalysis.TableCount, tableAnalysis.HasJoins, tableAnalysis.HasSubquery)

		// 聚合分析
		aggAnalysis := sqlparser.AnalyzeAggregation(stmt)
		fmt.Printf("  聚合分析: 包含聚合=%v, 聚合函数=%v\n",
			aggAnalysis.HasAggregation, aggAnalysis.Functions)

		// 查询复杂度分析
		complexity := sqlparser.AnalyzeQueryComplexity(stmt)
		fmt.Printf("  复杂度: %s (分数: %d)\n",
			complexity.ComplexityLevel, complexity.ComplexityScore)

		fmt.Println()
	}

	// LEFT JOIN示例
	fmt.Println("=== LEFT JOIN示例 ===")
	leftJoinQueries := []string{
		"SELECT u.name, p.title FROM users u LEFT JOIN posts p ON u.id = p.user_id",
		"SELECT u.name, COUNT(p.id) as post_count FROM users u LEFT JOIN posts p ON u.id = p.user_id GROUP BY u.id",
		"SELECT c.name, COUNT(p.id) as product_count FROM categories c LEFT JOIN products p ON c.id = p.category_id GROUP BY c.id",
		"SELECT u.name, o.total FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.active = 1",
	}

	for i, sql := range leftJoinQueries {
		fmt.Printf("LEFT JOIN示例 %d: %s\n", i+1, sql)
		
		stmt, err := sqlparser.Parse(sql)
		if err != nil {
			log.Printf("解析错误: %v", err)
			continue
		}

		tables := sqlparser.ExtractAllTables(stmt)
		fmt.Printf("  涉及的表: %v\n", tables)

		tableAnalysis := sqlparser.AnalyzeTables(stmt)
		fmt.Printf("  表分析: 表数量=%d, 有连接=%v, 有子查询=%v\n",
			tableAnalysis.TableCount, tableAnalysis.HasJoins, tableAnalysis.HasSubquery)

		aggAnalysis := sqlparser.AnalyzeAggregation(stmt)
		fmt.Printf("  聚合分析: 包含聚合=%v, 有GROUP BY=%v\n",
			aggAnalysis.HasAggregation, aggAnalysis.HasGroupBy)

		complexity := sqlparser.AnalyzeQueryComplexity(stmt)
		fmt.Printf("  复杂度: %s (分数: %d)\n",
			complexity.ComplexityLevel, complexity.ComplexityScore)

		fmt.Println()
	}

	// RIGHT JOIN示例
	fmt.Println("=== RIGHT JOIN示例 ===")
	rightJoinQueries := []string{
		"SELECT u.name, p.title FROM users u RIGHT JOIN posts p ON u.id = p.user_id",
		"SELECT p.name, c.name FROM products p RIGHT JOIN categories c ON p.category_id = c.id",
		"SELECT o.id, u.name FROM orders o RIGHT JOIN users u ON o.user_id = u.id",
	}

	for i, sql := range rightJoinQueries {
		fmt.Printf("RIGHT JOIN示例 %d: %s\n", i+1, sql)
		
		stmt, err := sqlparser.Parse(sql)
		if err != nil {
			log.Printf("解析错误: %v", err)
			continue
		}

		tables := sqlparser.ExtractAllTables(stmt)
		fmt.Printf("  涉及的表: %v\n", tables)

		tableAnalysis := sqlparser.AnalyzeTables(stmt)
		fmt.Printf("  表分析: 表数量=%d, 有连接=%v\n",
			tableAnalysis.TableCount, tableAnalysis.HasJoins)

		complexity := sqlparser.AnalyzeQueryComplexity(stmt)
		fmt.Printf("  复杂度: %s (分数: %d)\n",
			complexity.ComplexityLevel, complexity.ComplexityScore)

		fmt.Println()
	}

	// FULL OUTER JOIN示例（MySQL不直接支持，但可以用UNION模拟）
	fmt.Println("=== FULL OUTER JOIN模拟示例 ===")
	fullJoinQueries := []string{
		"SELECT u.name, p.title FROM users u LEFT JOIN posts p ON u.id = p.user_id UNION SELECT u.name, p.title FROM users u RIGHT JOIN posts p ON u.id = p.user_id",
	}

	for i, sql := range fullJoinQueries {
		fmt.Printf("FULL OUTER JOIN示例 %d: %s\n", i+1, sql)
		
		stmt, err := sqlparser.Parse(sql)
		if err != nil {
			log.Printf("解析错误: %v (UNION可能暂不支持)", err)
			continue
		}

		tables := sqlparser.ExtractAllTables(stmt)
		fmt.Printf("  涉及的表: %v\n", tables)

		complexity := sqlparser.AnalyzeQueryComplexity(stmt)
		fmt.Printf("  复杂度: %s (分数: %d)\n",
			complexity.ComplexityLevel, complexity.ComplexityScore)

		fmt.Println()
	}

	// 多表JOIN示例
	fmt.Println("=== 多表JOIN示例 ===")
	multiJoinQueries := []string{
		"SELECT u.name, p.title, c.name FROM users u JOIN posts p ON u.id = p.user_id JOIN categories c ON p.category_id = c.id",
		"SELECT o.id, u.name, p.name, c.name FROM orders o JOIN users u ON o.user_id = u.id JOIN products p ON o.product_id = p.id JOIN categories c ON p.category_id = c.id",
		"SELECT u.name, COUNT(p.id), AVG(r.rating) FROM users u LEFT JOIN posts p ON u.id = p.user_id LEFT JOIN reviews r ON p.id = r.post_id GROUP BY u.id",
	}

	for i, sql := range multiJoinQueries {
		fmt.Printf("多表JOIN示例 %d: %s\n", i+1, sql)
		
		stmt, err := sqlparser.Parse(sql)
		if err != nil {
			log.Printf("解析错误: %v", err)
			continue
		}

		tables := sqlparser.ExtractAllTables(stmt)
		fmt.Printf("  涉及的表: %v\n", tables)

		tableAnalysis := sqlparser.AnalyzeTables(stmt)
		fmt.Printf("  表分析: 表数量=%d, 有连接=%v, 有子查询=%v\n",
			tableAnalysis.TableCount, tableAnalysis.HasJoins, tableAnalysis.HasSubquery)

		aggAnalysis := sqlparser.AnalyzeAggregation(stmt)
		fmt.Printf("  聚合分析: 包含聚合=%v, 有GROUP BY=%v, 聚合函数=%v\n",
			aggAnalysis.HasAggregation, aggAnalysis.HasGroupBy, aggAnalysis.Functions)

		complexity := sqlparser.AnalyzeQueryComplexity(stmt)
		fmt.Printf("  复杂度: %s (分数: %d)\n",
			complexity.ComplexityLevel, complexity.ComplexityScore)

		fmt.Println()
	}

	// 自连接示例
	fmt.Println("=== 自连接示例 ===")
	selfJoinQueries := []string{
		"SELECT e1.name as employee, e2.name as manager FROM employees e1 LEFT JOIN employees e2 ON e1.manager_id = e2.id",
		"SELECT u1.name as user1, u2.name as user2 FROM users u1 JOIN friendships f ON u1.id = f.user1_id JOIN users u2 ON f.user2_id = u2.id",
		"SELECT p1.name as product, p2.name as related_product FROM products p1 JOIN product_relations pr ON p1.id = pr.product_id JOIN products p2 ON pr.related_product_id = p2.id",
	}

	for i, sql := range selfJoinQueries {
		fmt.Printf("自连接示例 %d: %s\n", i+1, sql)
		
		stmt, err := sqlparser.Parse(sql)
		if err != nil {
			log.Printf("解析错误: %v", err)
			continue
		}

		tables := sqlparser.ExtractAllTables(stmt)
		fmt.Printf("  涉及的表: %v\n", tables)

		tableAnalysis := sqlparser.AnalyzeTables(stmt)
		fmt.Printf("  表分析: 表数量=%d, 有连接=%v\n",
			tableAnalysis.TableCount, tableAnalysis.HasJoins)

		complexity := sqlparser.AnalyzeQueryComplexity(stmt)
		fmt.Printf("  复杂度: %s (分数: %d)\n",
			complexity.ComplexityLevel, complexity.ComplexityScore)

		fmt.Println()
	}
}